﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

public abstract class StoredProcedureProvider
{
    const string SQL_CLIENT = "System.Data.SqlClient";

    /// <summary>
    /// used for storing stored procedures parameters
    /// </summary>
    Dictionary<string, object> m_arguments;

    static ConnectionStringSettingsCollection connectionString;

    static StoredProcedureProvider()
    {
        connectionString = ConfigurationManager.ConnectionStrings;
    }

    protected StoredProcedureProvider(Dictionary<string, object> args = null)
    {
        m_arguments = args;
    }

    protected virtual string CommandText
    {
        get
        {
            return this.GetType().Name;
        }
    }

    protected virtual CommandType CommandType
    {
        get
        {
            return CommandType.StoredProcedure;
        }
    }

    protected virtual IDbConnection GetConnection()
    {
        ConnectionStringSettings css = connectionString[ConfigurationManager.AppSettings[typeof(StoredProcedureProvider).FullName]];
        return new SqlConnection(css.ConnectionString);
    }

    public virtual DataTable Read()
    {
        DataTable dt = new DataTable();
        using (var con = GetConnection())
        {
            using (IDbCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType;
                cmd.CommandText = CommandText;
                if (m_arguments != null)
                {
                    foreach (string key in m_arguments.Keys)
                    {
                        IDataParameter param = cmd.CreateParameter();
                        param.ParameterName = key;
                        param.Value = m_arguments[key] ?? DBNull.Value;
                        cmd.Parameters.Add(param);
                    }
                }
                con.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                }
            }
        }
        return dt;
    }

    public virtual object Scalar()
    {
        object ret;
        using (var con = GetConnection())
        {
            using (IDbCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType;
                cmd.CommandText = CommandText;
                if (m_arguments != null)
                {
                    foreach (string key in m_arguments.Keys)
                    {
                        IDataParameter param = cmd.CreateParameter();
                        param.ParameterName = key;
                        param.Value = m_arguments[key] ?? DBNull.Value;
                        cmd.Parameters.Add(param);
                    }
                }
                con.Open();
                ret = cmd.ExecuteScalar();
            }
        }
        return ret;
    }

    public virtual void Execute()
    {
        using (var con = GetConnection())
        {
            using (IDbCommand cmd = con.CreateCommand())
            {
                cmd.CommandType = CommandType;
                cmd.CommandText = CommandText;
                if (m_arguments != null)
                {
                    foreach (string key in m_arguments.Keys)
                    {
                        IDataParameter param = cmd.CreateParameter();
                        param.ParameterName = key;
                        param.Value = m_arguments[key] ?? DBNull.Value;
                        cmd.Parameters.Add(param);
                    }
                }
                con.Open();
                var reader = cmd.ExecuteNonQuery();
            }
        }
    }
}

